package com.yun.zf.admin.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.yun.zf.common.admin.dto.HousePactMo;
import com.yun.zf.common.admin.dto.house.HouseDetailAdminDto;
import com.yun.zf.common.admin.dto.house.PactHouseDto;
import com.yun.zf.common.admin.model.house.HouseListMo;
import com.yun.zf.common.admin.model.house.es.BasicEs;
import com.yun.zf.common.app.domain.ZfHouseBasic;
import com.yun.zf.common.app.dto.community.CommunityTotal;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.util.List;

/**
 * <p>
 * 房源表 Mapper 接口
 * </p>
 *
 * @author cj
 * @since 2021-01-29
 */
@Mapper
public interface ZfHouseBasicMapper extends BaseMapper<ZfHouseBasic> {

    @Select("<script>" +
            "SELECT hb.id, hb.region_name, hb.street_name ,hb.community,hb.record_name,hb.modified_time,hb.city_code,hb.province_code , hd.bedrooms,hd.living_room,hd.toilet,hd.area,hd.hoped_price ,hd.floor_tag,hd.total_floor,hd.direction,hd.house_number,hd.price_unit " +
            "from zf_house_basic hb  LEFT JOIN  zf_house_detail hd on  hb.id=hd.basic_id   WHERE  hb.type=#{type} and hb.record_user =#{agentNo}  and hb.city_code=#{cityCode} and hb.province_code=#{provCode} " +
            "</script>")
    public List<HouseListMo> getHouseListByAgent(@Param("provCode") Long provCode,@Param("cityCode") Long cityCode,@Param("agentNo") int agentNo, Page<HouseListMo> page,int type);

    @Select("<script>" +
            "SELECT hb.id, hb.region_name, hb.street_name ,hb.community,hb.record_name,hb.modified_time,hb.city_code,hb.province_code , hd.bedrooms,hd.living_room,hd.toilet,hd.area,hd.hoped_price ,hd.floor_tag,hd.total_floor,hd.direction,hd.house_number,hd.price_unit ,i.tags " +
            " from zf_house_basic hb  LEFT JOIN  zf_house_detail hd on  hb.id=hd.basic_id   LEFT JOIN zf_house_info i  ON hb.id=i.basic_id    WHERE hb.id!=#{houseId} and hb.type=#{type} AND hb.`status`='published' and hb.community_id=#{communityId}  and hb.city_code=#{cityCode} and hb.province_code=#{provCode} " +
            "</script>")
    public List<HouseListMo> getHouseListByCommunity(@Param("provCode") Long provCode,@Param("cityCode") Long cityCode,@Param("communityId") Long communityId, @Param("type") int type,Long houseId ,Page<HouseListMo> page);


    @Select("<script>" +
            " SELECT hb.id, hb.region_name, hb.street_name ,hb.community,hb.record_name,hb.modified_time,hb.city_code,hb.province_code , hd.bedrooms,hd.living_room,hd.toilet,hd.area,hd.hoped_price ,hd.floor_tag,hd.total_floor,hd.direction,hd.house_number,hd.price_unit" +
            " from zf_house_basic hb  LEFT JOIN  zf_house_detail hd on  hb.id=hd.basic_id  WHERE  hb.id in " +
            "<foreach item='item' index='index' collection='ids' open='(' separator=',' close=')' > " +
            "  #{item} " +
            "  </foreach>" +
            "and  hb.city_code=#{cityCode} and hb.province_code=#{provCode}  " +
            "</script>")
    public List<HouseListMo> getHouseList(Long provCode, Long cityCode, List<Long> ids);

    @Select("<script>" +
            " SELECT   hd.area ,hd.bedrooms,hd.living_room,hd.toilet,hd.basic_id, hb.unit,hb.room,hd.floor_tag  ,hb.home_owner ,hb.phone, hb.community_id ,hb.community,hb.record_name ,hb.record_user  " +
            "from zf_house_basic hb LEFT JOIN  zf_house_detail hd on hb.id= hd.basic_id  WHERE hb.status not in ('rented','sold','ordered','signing') AND  hb.id=#{houseId} and hb.city_code=#{cityCode} and hb.province_code=#{provCode} " +
            "</script>")
    public HousePactMo getHousePact(Long houseId,Long cityCode,Long provCode);


    @Select("<script>" +
            " SELECT count(1) total,type,community_id from zf_house_basic WHERE  " +
            "  city_code=#{cityCode} and  province_code =#{provCode} GROUP BY type ,community_id " +
            "</script>")
    public List<CommunityTotal> getHouseByType(Long provCode, Long cityCode);


    @Select("<script>" +
            " SELECT min(area) minArea ,MAX(area) maxArea ,MIN(hoped_price) minPrice ,MAX(hoped_price) maxPrice ,AVG(hoped_price) averagePrice  from zf_house_detail_0 d WHERE d.basic_id in ( " +
            "SELECT b.id from zf_house_basic_0 b WHERE  b.type =#{type} AND b.community_id=#{communityId}  AND   city_code=#{cityCode} and  province_code =#{provCode} " +
            ") AND   city_code=#{cityCode} and  province_code =#{provCode}" +
            "</script>")
    public CommunityTotal getDetailMaxAndMin(Long provCode, Long cityCode, Long communityId, int type);


    @Select("<script>" +
            " SELECT hb.id,hb.province_code,hb.city_code,hb.community,hb.community_id,hb.section,hb.unit,hb.room,hb.type,hb.region_name,hb.street_name,hb.`status`,hb.source ,hb.record_name,hb.push_time, \n" +
            "            hd.hoped_price,hd.area,hd.bedrooms,hd.living_room,hd.toilet,hd.total_floor,hd.floor_tag,hd.direction,hd.is_elevator,hd.house_number,hd.price_unit,hd.title,hd.average_price\n" +
            " from zf_house_basic hb LEFT JOIN " +
            "            zf_house_detail hd ON hb.id= hd.basic_id" +
            " WHERE hb.id=#{id} and hb.city_code=#{cityCode} and hb.province_code=#{provinceCode}" +
            "</script>")
    HouseDetailAdminDto getBasicInfo(Long id, Long provinceCode, Long cityCode);



    @Select("<script>" +
            " SELECT hb.city_name, hb.region_name,hb.street_name,d.area ,c.cert_type,c.cert_number from zf_house_basic hb LEFT JOIN zf_house_detail d  ON hb.id= d.basic_id " +
            "LEFT JOIN zf_house_certificate c on hb.id= c.basic_id " +
            "  where   hb.id=#{id} and hb.city_code=#{cityCode} and hb.province_code=#{provinceCode}" +
            "</script>")
    PactHouseDto getPactHouseInfo(Long id, Long provinceCode, Long cityCode);

    @Select("<script>" +
            " SELECT hb.id hid,hb.province_code,hb.city_code,hb.region_code,hb.street_code,hb.city_name,hb.region_name,hb.street_name ,hb.community_id,hb.community " +
            ",hb.`status`,hb.source,hb.type from zf_house_basic hb " +
            "  where   hb.id=#{houseId} and hb.city_code=#{cityCode} and hb.province_code=#{provCode}" +
            "</script>")
    BasicEs basicEs(Long houseId, Long provCode, Long cityCode);

    @Update(
            "UPDATE zf_house_basic set push_time = CURRENT_DATE ,`status`='published' where   id=#{houseId} and city_code=#{cityCode} and province_code=#{provCode} ")
    void  updatePushTime(Long houseId, Long provCode, Long cityCode);

}
